﻿using KSSModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using Microsoft.SqlServer.Dts.Runtime;

namespace job
{
    public class Helper
    {
        public static void sendEmailDelayDoc()
        {
            var dataContext = new KSSDBDataContext();
            try
            {
                var watingA1 = from ds in dataContext.tblDocs
                               join u in dataContext.tblUsers on ds.approver1 equals u.UserName
                               where ds.status == 0 && ds.approved1Date == null && (DateTime.Today - ds.createdDate.Date).Days >= 2
                            select u;
                var watingA2 = from ds in dataContext.tblDocs
                               join u in dataContext.tblUsers on ds.approver1 equals u.UserName
                               where ds.status == 10 && ds.approved2Date == null && (DateTime.Today - ds.approved1Date.Value.Date).Days >= 2
                               select u;
                var watingTQM = from ds in dataContext.tblDocs
                               join u in dataContext.tblUsers on ds.approver1 equals u.UserName
                                where ds.status == 20 && ds.approvedQMDate == null && (DateTime.Today - ds.approved2Date.Value.Date).Days >= 2
                               select u;
                string error = "";
                string sendEmailDetail = "";
                foreach (var item in watingA1)
                {
                    if (item != null)
                    {
                        Utilities.Email.SendMail("redharing_555@hotmail.com", "redharing_555@hotmail.com", "waiting for approval 1", "testing body", out error);
                    }
                    if (!string.IsNullOrEmpty(error))
                    {
                        sendEmailDetail = error;
                    }
                    else
                    {
                        sendEmailDetail = "send follow up email to approval 1 to " +item.FirstName;
                    }

                    KSSOnline.Controller.KSSMaster.AddLog(new tblLog
                                                              {
                                                                  username = "system",
                                                                  datetime = DateTime.Now,
                                                                  details = sendEmailDetail
                                                                     
                                                              });
                }
                foreach (var item in watingA2)
                {
                    if (item != null)
                    {
                        Utilities.Email.SendMail("redharing_555@hotmail.com", "redharing_555@hotmail.com", "waiting for approval 2", "testing body", out error);
                        if (!string.IsNullOrEmpty(error))
                        {
                            sendEmailDetail = error;
                        }
                        else
                        {
                            sendEmailDetail = "send follow up email to approval 2 to " + item.FirstName;
                        }
                        KSSOnline.Controller.KSSMaster.AddLog(new tblLog
                        {
                            username = "system",
                            datetime = DateTime.Now,
                            details = sendEmailDetail
                        });
                    }
                }
                foreach (var item in watingTQM)
                {
                    if (item != null)
                    {
                        Utilities.Email.SendMail("redharing_555@hotmail.com", "redharing_555@hotmail.com", "waiting for tqm", "testing body", out error);
                        if (!string.IsNullOrEmpty(error))
                        {
                            sendEmailDetail = error;
                        }
                        else
                        {
                            sendEmailDetail = "send follow up email to tqm to " + item.FirstName;
                        }
                        KSSOnline.Controller.KSSMaster.AddLog(new tblLog
                        {
                            username = "system",
                            datetime = DateTime.Now,
                            details = sendEmailDetail
                        });
                    }
                }

            }
            catch (Exception ex)
            {
                
            }
        }
        public static void excuteDST(string path, out string output)
        {

            output = "";
            Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();

            // Load package from file system (use LoadFromSqlServer for SQL Server based packages)

            Package myPackage = myApplication.LoadPackage(path, null);

            // Optional set the value from one of the SSIS package variables
            //myPackage.Variables["User::myVar"].Value = "test123";


            // Execute package
            DTSExecResult myResult = myPackage.Execute();
            if (myResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {

                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in myPackage.Errors)
                {

                    output += local_DtsError.Description.ToString() + Environment.NewLine;
                }

            }
            else
            {
                // Show the execution result
                output = myResult.ToString();
            }
        }



    }
}
